package com.liferay.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.liferay.contant.Contants;
import com.liferay.dao.ConnectDao;
import com.liferay.dao.EditUserDao;
import com.liferay.model.Faculty;
import com.liferay.model.Users;

public class EditUserDaoImpl implements EditUserDao {
	
	
	public Connection connection;

	private final String GET_ALL_USER = "SELECT u.userId, firstName, middleName, lastName , emailAddress, uf.idFaculty FROM user_ u LEFT JOIN users_facultys uf on u.userId=uf.userId WHERE u.emailAddress not like 'admin%' ORDER BY createDate DESC;";
	private final String GET_ALL_FACULTY = "SELECT idFaculty,nameFaculty FROM nlu_faculty;";

	@Override
	public List<Users> getListUser() {
		connection = ConnectDao.getConnection();
		List<Users> list = new ArrayList<Users>();
		try {
			PreparedStatement preSta = connection
					.prepareStatement(GET_ALL_USER);
			ResultSet rs = preSta.executeQuery();

			while (rs.next()) {
				Users users = new Users();
				users.setIdUser(rs.getLong("userId"));
				users.setUserName(rs.getString("lastName") + " "
						+ rs.getString("middleName") + " "
						+ rs.getString("firstName"));
				users.setEmail(rs.getString("emailAddress"));
				if(rs.getLong("idFaculty")>0){
					users.setFkFaculty(rs.getLong("idFaculty"));
				}
				list.add(users);
			}
			rs.close();
			preSta.close();
			ConnectDao.close();
		} catch (Exception e) {
			e.printStackTrace();
			return list;
		}
		return list;
	}

	@Override
	public List<Faculty> getListFaculty() {
		connection = ConnectDao.getConnection();
		List<Faculty> list = new ArrayList<Faculty>();
		try {
			PreparedStatement preSta = connection
					.prepareStatement(GET_ALL_FACULTY);
			ResultSet rs = preSta.executeQuery();

			while (rs.next()) {
				Faculty faculty = new Faculty();
				faculty.setIdFaculty(rs.getLong("idFaculty"));
				faculty.setNameFaculty(rs.getString("nameFaculty"));
				list.add(faculty);
			}
			rs.close();
			preSta.close();
			ConnectDao.close();
		} catch (Exception e) {
			e.printStackTrace();
			return list;
		}
		return list;
	}

	@Override
	public String updateInformation(Users user) {
		try {
			connection = ConnectDao.getConnection();
			String sqlCheck = "SELECT COUNT(*) as number FROM users_facultys WHERE userId=?;";
			PreparedStatement preSta = connection.prepareStatement(sqlCheck);
			preSta.setLong(1, user.getIdUser());
			ResultSet res = preSta.executeQuery();
			int size= 0;
			while(res.next()){
				size = res.getInt("number");
				break;
			}
			preSta.close();
			if(size>0){
				String sql = "UPDATE users_facultys SET idFaculty=? WHERE userId=?;";
				PreparedStatement preSta1 = connection.prepareStatement(sql);
				preSta1.setLong(1, user.getFkFaculty());
				preSta1.setLong(2, user.getIdUser());
				preSta1.executeUpdate();
				preSta1.close();
			}else{
				String sql = "INSERT INTO users_facultys(userId,idFaculty) VALUES(?,?);";
				PreparedStatement preSta1 = connection.prepareStatement(sql);
				preSta1.setLong(1, user.getIdUser());
				preSta1.setLong(2, user.getFkFaculty());
				preSta1.executeUpdate();
				preSta1.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return Contants.ERROR;
		}finally{
			if(connection!=null){
				try {
					ConnectDao.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return Contants.SUCCESS;
	}
}
